-- @owner: taoying
-- @date: 2025-9-26
-- @testpoint: ubtree索引(指定PCR)_事务中索引列DML，提交

--step1:建表;expect:成功
drop table if exists t_ubtree_0052 cascade;
create table t_ubtree_0052 (
id int,
emp_name varchar(50) not null,
department varchar(20),
salary numeric(10,2),
join_date date
) with (storage_type=ustore);

--step2:建索引时指定PCR;expect:成功
create index i_ubtree_0052 on t_ubtree_0052 using ubtree(department, salary) with (index_type='pcr');

--step3:查看索引类型;expect:成功
select relkind, reloptions from pg_class where relname like 'i_ubtree_0052%';

--step4:插入数据;expect:成功
insert into t_ubtree_0052 values (1, 'dev', 'Alice', 80000.00),
(2, 'test', 'Bob', 75000.00), (3, 'dev', 'Charlie', 82000.00);

--step5:事务操作（DML+提交）;expect:成功
begin;/
insert into t_ubtree_0052 values(4, 'ops', 'David', 78000.00);
update t_ubtree_0052 set id = 100 where id = 2;
delete from t_ubtree_0052 where id = 3;
select * from t_ubtree_0052 order by id;
commit;

--step6:查询计划;expect:成功
set enable_seqscan = off;
explain (costs off) select * from t_ubtree_0052 order by id;

--step7:清理环境;expect:成功
drop table if exists t_ubtree_0052 cascade;
set enable_seqscan = on;